﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitor_DbUsage]
#-- Purpose:		Log the size of data files and disks for historical review
#--	Last Update:	04/16/2013
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitor_DbUsage]
AS

SET NOCOUNT ON
DECLARE @sAuditSQL nvarchar(MAX), @db_name sysname

--- Audit the Database File Information for Historical Review
BEGIN TRY
	--- Grab the Current Disk Allocations of all Database Files
	DECLARE @t TABLE (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, name sysname, physical_name nchar(520))
	DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
		SELECT		db.name
		FROM		[master].[sys].[databases] db
		WHERE		state = 0
	OPEN crs
	FETCH NEXT FROM crs INTO @db_name
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		SELECT	@sAuditSQL = 'USE ' + QUOTENAME(@db_name) + '; DBCC SHOWFILESTATS WITH NO_INFOMSGS'
		INSERT INTO @t EXEC (@sAuditSQL)

		FETCH NEXT FROM crs INTO @db_name
	  END
	CLOSE crs
	DEALLOCATE crs

	--- Store the Database File Information for Historical Review
	INSERT INTO [dbo].[tblMaint_DatabaseFiles](dbf_database_name, dbf_log_date, dbf_name, dbf_physical_name, dbf_type, dbf_sizeMB, dbf_free_sizeMB, dbf_max_sizeMB, dbf_growthMB, dbf_is_percent_growth)
		SELECT		DB_NAME(mf.database_id),
					GETDATE(),
					mf.name,
					mf.physical_name,
					mf.type,
					CAST(mf.size as decimal(18, 2)) * 8 / 1024,
					CAST((t1.TotalExtents - t1.UsedExtents) * (mf.size / t1.TotalExtents) as decimal(18, 2)) * 8 / 1024,
					CAST(mf.max_size as decimal(18, 2)) * 8 / 1024,
					CAST(mf.growth as decimal(18, 2)) * 8 / 1024,
					mf.is_percent_growth
		FROM		[sys].[master_files] mf (NOLOCK)
		JOIN		@t t1 ON mf.name = t1.name
						AND mf.physical_name = t1.physical_name
		WHERE		DB_NAME(mf.database_id) <> 'tempdb'
		ORDER BY	DB_NAME(mf.database_id),
					mf.file_id
END TRY
BEGIN CATCH
	PRINT 'Failed to audit database file sizes'
	PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
	PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

	--- Add Failure Audit
	SELECT @sAuditSQL = 'INSERT INTO [dbo].[tblMaint_Log] (mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ CAST(GETDATE() as varchar(100)) + ''', ''audit'', 0, ''@@RESULTS@@'')'
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to audit database file sizes' + CHAR(13)
					+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
					+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
					+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
					+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
	ELSE
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to audit database file sizes')
	EXEC (@sAuditSQL)
END CATCH

--- Cleanup Duplicate Records from Newer Dates
BEGIN TRY
	;WITH df AS
	(
		SELECT		ROW_NUMBER() OVER(ORDER BY dbf_database_name, dbf_physical_name, dbf_log_date) iRecNumber,
					dbf_id,
					dbf_database_name,
					dbf_physical_name,
					dbf_type,
					dbf_sizeMB,
					dbf_free_sizeMB,
					dbf_max_sizeMB,
					dbf_growthMB,
					dbf_is_percent_growth
		FROM		[dbo].[tblMaint_DatabaseFiles]
	)
	DELETE FROM	t2
	FROM		df t1
	JOIN		df t2 ON t1.iRecNumber = (t2.iRecNumber - 1)
					AND t1.dbf_database_name = t2.dbf_database_name
					AND t1.dbf_physical_name = t2.dbf_physical_name
					AND t1.dbf_type = t2.dbf_type
					AND t1.dbf_sizeMB = t2.dbf_sizeMB
					AND t1.dbf_free_sizeMB = t2.dbf_free_sizeMB
					AND t1.dbf_max_sizeMB = t2.dbf_max_sizeMB
					AND t1.dbf_growthMB = t2.dbf_growthMB
					AND t1.dbf_is_percent_growth = t2.dbf_is_percent_growth
END TRY
BEGIN CATCH
	PRINT 'Failed to cleanup duplicate database file size records from newer dates'
	PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
	PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

	--- Add Failure Audit
	SELECT @sAuditSQL = 'INSERT INTO [dbo].[tblMaint_Log] (mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ CAST(GETDATE() as varchar(100)) + ''', ''audit'', 0, ''@@RESULTS@@'')'
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to cleanup duplicate database file size records from newer dates' + CHAR(13)
					+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
					+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
					+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
					+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
	ELSE
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to cleanup duplicate database file size records from newer dates')
	EXEC (@sAuditSQL)
END CATCH

--- Audit Database Disk Usage Information
BEGIN TRY
	DECLARE @d TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
	INSERT INTO @d EXEC [dbo].[spIO_GetVolumeSpace] ''
	INSERT INTO [dbo].[tblMaint_DatabaseDisk] (dbd_log_date, dbd_drive, dbd_capacityMB, dbd_usedMB, dbd_freeMB, dbd_free_pct)
		SELECT	DISTINCT
				GETDATE() dbd_log_date,
				dd.dbd_drive,
				CAST(dd.dbd_capacityMB as decimal) dbd_capacityMB,
				CAST(dd.dbd_usedMB as decimal) dbd_usedMB,
				CAST(dd.dbd_freeMB as decimal) dbd_freeMB,
				CAST(dd.dbd_free_pct as decimal) dbd_free_pct
		FROM	@d dd
		JOIN	@t df ON df.physical_name LIKE dd.dbd_drive + '%'
		WHERE	dd.dbd_capacityMB <> 'NaN'
					AND dd.dbd_usedMB <> 'NaN'
					AND dd.dbd_freeMB <> 'NaN'
					AND dd.dbd_free_pct <> 'NaN'
END TRY
BEGIN CATCH
	PRINT 'Failed to audit database disk sizes'
	PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
	PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

	--- Add Failure Audit
	SELECT @sAuditSQL = 'INSERT INTO [dbo].[tblMaint_Log] (mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ CAST(GETDATE() as varchar(100)) + ''', ''audit'', 0, ''@@RESULTS@@'')'
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to audit database disk sizes' + CHAR(13)
					+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
					+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
					+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
					+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
	ELSE
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to audit database disk sizes')
	EXEC (@sAuditSQL)
END CATCH

--- Cleanup Duplicate Records from Newer Dates
BEGIN TRY
	;WITH df AS
	(
		SELECT		ROW_NUMBER() OVER(ORDER BY dbd_drive, dbd_log_date) iRecNumber,
					dbd_id,
					dbd_drive,
					dbd_capacityMB,
					dbd_usedMB,
					dbd_freeMB,
					dbd_free_pct
		FROM		[dbo].[tblMaint_DatabaseDisk]
	)
	DELETE FROM	t2
	FROM		df t1
	JOIN		df t2 ON t1.iRecNumber = (t2.iRecNumber - 1)
					AND t1.dbd_drive = t2.dbd_drive
					AND t1.dbd_capacityMB = t2.dbd_capacityMB
					AND t1.dbd_usedMB = t2.dbd_usedMB
					AND t1.dbd_freeMB = t2.dbd_freeMB
					AND t1.dbd_free_pct = t2.dbd_free_pct
END TRY
BEGIN CATCH
	PRINT 'Failed to cleanup duplicate disk size records from newer dates'
	PRINT '   Error: ' + CAST(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE()
	PRINT '   Procedure: ' + ERROR_PROCEDURE() + ' (line: ' + CAST(ERROR_LINE() as varchar(10)) + ')'

	--- Add Failure Audit
	SELECT @sAuditSQL = 'INSERT INTO [dbo].[tblMaint_Log] (mnt_action_date, mnt_action_type, mnt_success, mnt_results) VALUES (''' 
						+ CAST(GETDATE() as varchar(100)) + ''', ''audit'', 0, ''@@RESULTS@@'')'
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to cleanup duplicate disk size records from newer dates' + CHAR(13)
					+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
					+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
					+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
					+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), ''))
	ELSE
		SELECT @sAuditSQL = REPLACE(@sAuditSQL, '@@RESULTS@@', 'Failed to cleanup duplicate disk size records from newer dates')
	EXEC (@sAuditSQL)
END CATCH
					
SET NOCOUNT OFF